package org.mebusw.dao.impl;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.mebusw.dao.IGameTableDAO;
import org.mebusw.davincicode.GameStatus;
import org.mebusw.vo.GameBrief;
import org.mebusw.vo.GameTable;

/**
 * @see /mysql/createDB.sql
 * 
 * @author j1shen
 * 
 */
public class IGameTableDAOImpl implements IGameTableDAO {
	private Connection conn = null;

	public IGameTableDAOImpl(Connection conn) {
		this.conn = conn;
	}

	public boolean doCreate(GameTable gameTable) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql;
		int currentMaxGid = 0;

		try {
			sql = "SELECT MAX(gid) from games";
			pstmt = this.conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				currentMaxGid = rs.getInt(1);
				gameTable.setGid(currentMaxGid + 1);
			}

			sql = "INSERT INTO games(data, gid, gameDesc, playerCount, gameStatus, "
					+ "players, gameName, lastMove, createDate) "
					+ "VALUES (?,?,?,?,?,?,?,?,?)";
			pstmt = this.conn.prepareStatement(sql);

			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			ObjectOutputStream oos = new ObjectOutputStream(baos);
			oos.writeObject(gameTable);
			oos.flush();
			ByteArrayInputStream bais = new ByteArrayInputStream(baos
					.toByteArray());

			System.out.println("size of baos: " + baos.size());
			System.out.println("available of bais: " + bais.available());
			pstmt.setBinaryStream(1, bais, bais.available());
			oos.close();
			
			pstmt.setInt(2, gameTable.getGid());
			pstmt.setString(3, gameTable.getDescription());
			pstmt.setInt(4, gameTable.getPlayerCount());
			pstmt.setString(5, "");
			pstmt.setString(6, "");
			pstmt.setString(7, gameTable.getGameName());
			pstmt.setString(8, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
					.format(new Date()));
			pstmt.setString(9, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
					.format(new Date()));

			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
			bais.close();
			baos.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {

				pstmt.close();

			} catch (Exception e) {

			}
		}
		return flag;
	}

	public boolean doDelete(int gid) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "DELETE FROM games WHERE gid=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, gid);
			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {

			}
		}

		return flag;
	}

	public boolean doUpdate(GameTable gameTable) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "UPDATE games SET data=?, gameStatus=?, playerCount=?, "
				+ "players=?, lastMove=?, currPlayer=? WHERE gid=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			ObjectOutputStream oos = new ObjectOutputStream(baos);
			oos.writeObject(gameTable);
			oos.flush();
			ByteArrayInputStream bais = new ByteArrayInputStream(baos
					.toByteArray());
			System.out.println("doUpdate():available:" + bais.available());
			System.out.println("doUpdate():size:" + baos.size());
			pstmt.setBinaryStream(1, bais, bais.available());
			oos.close();
			baos.close();
			bais.close();
			pstmt.setString(2, GameStatus.toString(gameTable.getState())
					+ gameTable.getPlayers().get(gameTable.getCurrPlayer())
							.getName());
			pstmt.setInt(3, gameTable.getPlayers().size());
			StringBuilder sb = new StringBuilder(gameTable.getPlayers().get(0)
					.getName());
			for (int i = 1; i < gameTable.getPlayers().size(); i++) {
				sb.append(", ");
				sb.append(gameTable.getPlayers().get(i).getName());
			}
			pstmt.setString(4, sb.toString());
			pstmt.setString(5, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss ")
					.format(new Date()));

			pstmt.setInt(6, gameTable.getCurrPlayer());
			pstmt.setInt(7, gameTable.getGid());

			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {

			}
		}

		return flag;
	}

	public GameTable findById(int gid) throws Exception {
		GameTable gameTable = null;
		PreparedStatement pstmt = null;
		String sql = "SELECT data FROM games WHERE gid=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, gid);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				ObjectInputStream ois = new ObjectInputStream(rs
						.getBinaryStream(1));

				System.out.println("findById.available: "
						+ rs.getBinaryStream(1).available());
				gameTable = (GameTable) ois.readObject();
				ois.close();
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return gameTable;
	}

	public int getAllCount(String keyword) throws Exception {
		PreparedStatement pstmt = null;
		String sql;
		int count = 0;

		try {
			sql = "select COUNT(uid) from user "
					+ "where uid like upper(?) or username like upper(?) or score like upper(?) ";

			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, "%" + keyword + "%");
			pstmt.setString(2, "%" + keyword + "%");
			pstmt.setString(3, "%" + keyword + "%");

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				count = rs.getInt(1);
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return count;
	}

	public List<GameBrief> findAll() throws Exception {
		List<GameBrief> allGame = new ArrayList<GameBrief>();
		GameBrief gb = null;
		PreparedStatement pstmt = null;
		String sql;

		try {
			// for MySQL statement
			sql = "SET @cnt = 0";
			pstmt = this.conn.prepareStatement(sql);
			pstmt.executeQuery();

			sql = "select * from ("
					+ "select (@cnt := @cnt + 1) as rn, gid, gameDesc, gameStatus, "
					+ "playerCount, players, gameName, lastMove, createDate "
					+ "from games) as temp";

			pstmt = this.conn.prepareStatement(sql);

			ResultSet rs = pstmt.executeQuery();
			// int rn;
			while (rs.next()) {
				// rn = rs.getInt(1);
				gb = new GameBrief();
				gb.setGid(rs.getInt(2));
				gb.setDescription(rs.getString(3));
				gb.setGameStatus(rs.getString(4));
				gb.setPlayerCount(rs.getInt(5));
				gb.setPlayers(rs.getString(6));
				gb.setGameName(rs.getString(7));
				gb.setLastMove(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
						.parse(rs.getString(8)));
				gb.setCreateDate(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
						.parse(rs.getString(9)));
				allGame.add(gb);

			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return allGame;
	}

	public int getAllCount() throws Exception {
		PreparedStatement pstmt = null;
		String sql;
		int count = 0;

		try {
			sql = "select COUNT(gid) from games ";
			pstmt = this.conn.prepareStatement(sql);

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				count = rs.getInt(1);
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return count;
	}

	public List<GameTable> findByName(String playerName) throws Exception {
		List<GameTable> allGame = new ArrayList<GameTable>();
		GameTable gameTable = null;
		PreparedStatement pstmt = null;
		String sql = "SELECT data FROM games WHERE players like ?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, "%" + playerName + "%");
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				ObjectInputStream ois = new ObjectInputStream(rs
						.getBinaryStream(1));

				gameTable = (GameTable) ois.readObject();
				ois.close();

				allGame.add(gameTable);
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return allGame;
	}

}
